from datetime import datetime
import json
import time
import pandas as pd
from dao.mqtthelp_v3 import MQTTClient
import openpyxl
import os

# 初始化MQTT客户端并订阅话题
mqtt_client = MQTTClient()
topic = "/link/objects_list"
mqtt_client.subscribe_topic(topic)

# 初始化北向和南向速度列表
speeds_north = []
speeds_south = []

# 文件名
file_name = "speed_data.xlsx"

# 如果Excel文件不存在，创建一个新的文件
if not os.path.isfile(file_name):
    df_empty = pd.DataFrame(columns=["Time", "Direction", "Count", "Average Speed"])
    df_empty.to_excel(file_name, index=False, sheet_name='Sheet1')

def write_to_excel(file_name, data):
    # 加载现有工作簿
    if os.path.isfile(file_name):
        book = openpyxl.load_workbook(file_name)
    else:
        # 创建新的工作簿和工作表
        book = openpyxl.Workbook()
        sheet = book.active
        sheet.title = 'Sheet1'

    # 选择工作表
    sheet = book['Sheet1']

    # 确定写入起始行
    startrow = sheet.max_row

    # 逐行写入数据
    for row in data:
        sheet.append([row['Time'], row['Direction'], row['Count'], row['Average Speed']])

    # 保存工作簿
    book.save(file_name)

# 获取订阅的消息内容
while True:
    try:
        received_messages = mqtt_client.get_received_messages()
        if received_messages:
            # 获取当前时间并输出
            current_time = datetime.now()
            # print("Current time:", current_time)

            # 解析收到的消息
            data = json.loads(received_messages[0]['payload'])
            # print("车辆总数:", len(data['objects_list']))

            # 提取总的速度数据
            speeds = [obj["speed"] for obj in data["objects_list"]]

            # 清空速度列表
            speeds_north.clear()
            speeds_south.clear()

            # 将速度数据分组存入北向和南向列表
            for obj in data["objects_list"]:
                device_num = int(obj["device_num"][2:].lstrip("0"))
                if 1 <= device_num <= 31:  # 北向
                    speeds_north.append(obj["speed"])
                elif 32 <= device_num <= 65:  # 南向
                    speeds_south.append(obj["speed"])

            # 计算北向平均速度并输出
            if speeds_north:
                average_speed_north = round(sum(speeds_north) / len(speeds_north), 2)
                # print("北向车辆数:", len(speeds_north))
                # print("北向平均速度:", average_speed_north)
                north_data = {"Time": current_time.strftime('%m/%d %H:%M'), "Direction": "North", "Count": len(speeds_north), "Average Speed": average_speed_north}
            else:
                #print("没有北向速度数据")
                north_data = {"Time": current_time.strftime('%m/%d %H:%M'), "Direction": "North", "Count": 0, "Average Speed": 0}

            # 计算南向平均速度并输出
            if speeds_south:
                average_speed_south = round(sum(speeds_south) / len(speeds_south), 2)
                # print("南向车辆数:", len(speeds_south))
                # print("南向平均速度:", average_speed_south)
                south_data = {"Time": current_time.strftime('%m/%d %H:%M'), "Direction": "South", "Count": len(speeds_south), "Average Speed": average_speed_south}
            else:
                # print("没有南向速度数据")
                south_data = {"Time": current_time.strftime('%m/%d %H:%M'), "Direction": "South", "Count": 0, "Average Speed": 0}

            # 将数据写入Excel文件
            write_to_excel(file_name, [north_data, south_data])

            mqtt_client.received_messages = []

        time.sleep(30)
    except Exception as e:
        print(f"发生异常: {e}")
        time.sleep(10)  # 暂停一段时间后继续尝试

# 隧道生产202服务器上跑，python版本高，已经适配了